﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using PS.DBUtility;
namespace PS.DAL
{
    //机构部室考核信息表
    public partial class KPIInsInfo
    {

        public bool Exists(int KPIInsInfoID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from KPIInsInfo");
            strSql.Append(" where ValidMark=1 and ");
            strSql.Append(" KPIInsInfoID = @KPIInsInfoID  ");
            SqlParameter[] parameters = {
					new SqlParameter("@KPIInsInfoID", SqlDbType.Int,4)
};
            parameters[0].Value = KPIInsInfoID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(PS.Model.KPIInsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into KPIInsInfo(");
            strSql.Append("Comments,DispOrder,Creator,CreateTime,Editor,EditTime,DepID,AssessInd,AssessTimes,Weight_M,Weight_S,Weight_H,Weight_Y,Indicator,AssessCriteria,SourceFromDeptID,EvaluatorID,ValidMark");
            strSql.Append(") values (");
            strSql.Append("@Comments,@DispOrder,@Creator,@CreateTime,@Editor,@EditTime,@DepID,@AssessInd,@AssessTimes,@Weight_M,@Weight_S,@Weight_H,@Weight_Y,@Indicator,@AssessCriteria,@SourceFromDeptID,@EvaluatorID,@ValidMark");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@Comments", SqlDbType.VarChar,2000) ,            
                        new SqlParameter("@DispOrder", SqlDbType.Int,4) ,            
                        new SqlParameter("@Creator", SqlDbType.Int,4) ,            
                        new SqlParameter("@CreateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Editor", SqlDbType.Int,4) ,            
                        new SqlParameter("@EditTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DepID", SqlDbType.Int,4) ,            
                        new SqlParameter("@AssessInd", SqlDbType.VarChar,2000) ,            
                        new SqlParameter("@AssessTimes", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Weight_M", SqlDbType.Decimal,9) , 
                        new SqlParameter("@Weight_S", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Weight_H", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Weight_Y", SqlDbType.Decimal,9) ,            
           
                        new SqlParameter("@Indicator", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@AssessCriteria", SqlDbType.VarChar,2000) ,            
                        new SqlParameter("@SourceFromDeptID", SqlDbType.Int,4) ,            
                        new SqlParameter("@EvaluatorID", SqlDbType.Int,4)   ,            
                        new SqlParameter("@ValidMark", SqlDbType.Int,4)             
              
            };

            parameters[0].Value = model.Comments;
            parameters[1].Value = model.DispOrder;
            parameters[2].Value = model.Creator;
            parameters[3].Value = model.CreateTime;
            parameters[4].Value = model.Editor;
            parameters[5].Value = model.EditTime;
            parameters[6].Value = model.DepID;
            parameters[7].Value = model.AssessInd;
            parameters[8].Value = model.AssessTimes;
            parameters[9].Value = model.Weight_M;
            parameters[10].Value = model.Weight_S;
            parameters[11].Value = model.Weight_H;
            parameters[12].Value = model.Weight_Y;
            parameters[13].Value = model.Indicator;
            parameters[14].Value = model.AssessCriteria;
            parameters[15].Value = model.SourceFromDeptID;
            parameters[16].Value = model.EvaluatorID;
            parameters[17].Value = model.ValidMark;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(PS.Model.KPIInsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update KPIInsInfo set ");

            strSql.Append(" Comments = @Comments , ");
            strSql.Append(" DispOrder = @DispOrder , ");
            strSql.Append(" Creator = @Creator , ");
            strSql.Append(" CreateTime = @CreateTime , ");
            strSql.Append(" Editor = @Editor , ");
            strSql.Append(" EditTime = @EditTime , ");
            strSql.Append(" DepID = @DepID , ");
            strSql.Append(" AssessInd = @AssessInd , ");
            strSql.Append(" AssessTimes = @AssessTimes , ");
            strSql.Append(" Weight_M = @Weight_M , ");
            strSql.Append(" Weight_S = @Weight_S , ");
            strSql.Append(" Weight_H = @Weight_H , ");
            strSql.Append(" Weight_Y = @Weight_Y , ");

            strSql.Append(" Indicator = @Indicator , ");
            strSql.Append(" AssessCriteria = @AssessCriteria , ");
            strSql.Append(" SourceFromDeptID = @SourceFromDeptID , ");
            strSql.Append(" ValidMark = @ValidMark , ");
            strSql.Append(" EvaluatorID = @EvaluatorID  ");
            strSql.Append(" where KPIInsInfoID=@KPIInsInfoID ");

            SqlParameter[] parameters = {
			            new SqlParameter("@KPIInsInfoID", SqlDbType.Int,4) ,            
                        new SqlParameter("@Comments", SqlDbType.VarChar,2000) ,            
                        new SqlParameter("@DispOrder", SqlDbType.Int,4) ,            
                        new SqlParameter("@Creator", SqlDbType.Int,4) ,            
                        new SqlParameter("@CreateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Editor", SqlDbType.Int,4) ,            
                        new SqlParameter("@EditTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DepID", SqlDbType.Int,4) ,            
                        new SqlParameter("@AssessInd", SqlDbType.VarChar,2000) ,            
                        new SqlParameter("@AssessTimes", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Weight_M", SqlDbType.Decimal,9) ,  
                        new SqlParameter("@Weight_S", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Weight_H", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Weight_Y", SqlDbType.Decimal,9) ,            
          
                        new SqlParameter("@Indicator", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@AssessCriteria", SqlDbType.VarChar,2000) ,            
                        new SqlParameter("@SourceFromDeptID", SqlDbType.Int,4) ,            
                        new SqlParameter("@EvaluatorID", SqlDbType.Int,4)   ,            
                        new SqlParameter("@ValidMark", SqlDbType.Int,4)            
              
            };

            parameters[0].Value = model.KPIInsInfoID;
            parameters[1].Value = model.Comments;
            parameters[2].Value = model.DispOrder;
            parameters[3].Value = model.Creator;
            parameters[4].Value = model.CreateTime;
            parameters[5].Value = model.Editor;
            parameters[6].Value = model.EditTime;
            parameters[7].Value = model.DepID;
            parameters[8].Value = model.AssessInd;
            parameters[9].Value = model.AssessTimes;
            parameters[10].Value = model.Weight_M;
            parameters[11].Value = model.Weight_S;
            parameters[12].Value = model.Weight_H;
            parameters[13].Value = model.Weight_Y;
            parameters[14].Value = model.Indicator;
            parameters[15].Value = model.AssessCriteria;
            parameters[16].Value = model.SourceFromDeptID;
            parameters[17].Value = model.EvaluatorID;
            parameters[18].Value = model.ValidMark;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int KPIInsInfoID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update KPIInsInfo set ValidMark=0 ");
            strSql.Append(" where KPIInsInfoID=@KPIInsInfoID");
            SqlParameter[] parameters = {
					new SqlParameter("@KPIInsInfoID", SqlDbType.Int,4)
};
            parameters[0].Value = KPIInsInfoID;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string KPIInsInfoIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from KPIInsInfo ");
            strSql.Append(" where ID in (" + KPIInsInfoIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PS.Model.KPIInsInfo GetModel(int KPIInsInfoID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select KPIInsInfoID, Comments, DispOrder, Creator, CreateTime, Editor, EditTime, DepID, AssessInd, AssessTimes, Weight_M,Weight_S,Weight_H,Weight_Y, Indicator, AssessCriteria, SourceFromDeptID, EvaluatorID,ValidMark  ");
            strSql.Append("  from KPIInsInfo ");
            strSql.Append(" where KPIInsInfoID=@KPIInsInfoID");
            SqlParameter[] parameters = {
					new SqlParameter("@KPIInsInfoID", SqlDbType.Int,4)
};
            parameters[0].Value = KPIInsInfoID;


            PS.Model.KPIInsInfo model = new PS.Model.KPIInsInfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["KPIInsInfoID"].ToString() != "")
                {
                    model.KPIInsInfoID = int.Parse(ds.Tables[0].Rows[0]["KPIInsInfoID"].ToString());
                }
                model.Comments = ds.Tables[0].Rows[0]["Comments"].ToString();
                if (ds.Tables[0].Rows[0]["DispOrder"].ToString() != "")
                {
                    model.DispOrder = int.Parse(ds.Tables[0].Rows[0]["DispOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Creator"].ToString() != "")
                {
                    model.Creator = int.Parse(ds.Tables[0].Rows[0]["Creator"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Editor"].ToString() != "")
                {
                    model.Editor = int.Parse(ds.Tables[0].Rows[0]["Editor"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EditTime"].ToString() != "")
                {
                    model.EditTime = DateTime.Parse(ds.Tables[0].Rows[0]["EditTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DepID"].ToString() != "")
                {
                    model.DepID = int.Parse(ds.Tables[0].Rows[0]["DepID"].ToString());
                }
                model.AssessInd = ds.Tables[0].Rows[0]["AssessInd"].ToString();
                model.AssessTimes = ds.Tables[0].Rows[0]["AssessTimes"].ToString();
                if (ds.Tables[0].Rows[0]["Weight_M"].ToString() != "")
                {
                    model.Weight_M = decimal.Parse(ds.Tables[0].Rows[0]["Weight_M"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Weight_S"].ToString() != "")
                {
                    model.Weight_S = decimal.Parse(ds.Tables[0].Rows[0]["Weight_S"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Weight_H"].ToString() != "")
                {
                    model.Weight_H = decimal.Parse(ds.Tables[0].Rows[0]["Weight_H"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Weight_Y"].ToString() != "")
                {
                    model.Weight_Y = decimal.Parse(ds.Tables[0].Rows[0]["Weight_Y"].ToString());
                }
                model.Indicator = ds.Tables[0].Rows[0]["Indicator"].ToString();
                model.AssessCriteria = ds.Tables[0].Rows[0]["AssessCriteria"].ToString();
                if (ds.Tables[0].Rows[0]["SourceFromDeptID"].ToString() != "")
                {
                    model.SourceFromDeptID = int.Parse(ds.Tables[0].Rows[0]["SourceFromDeptID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EvaluatorID"].ToString() != "")
                {
                    model.EvaluatorID = int.Parse(ds.Tables[0].Rows[0]["EvaluatorID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ValidMark"].ToString() != "")
                {
                    model.ValidMark = int.Parse(ds.Tables[0].Rows[0]["ValidMark"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM KPIInsInfo where ValidMark=1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM KPIInsInfo where ValidMark=1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and  " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取某个部门的考核设置
        /// </summary>
        /// <param name="dept"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet GetList(string dept, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder strSql = new StringBuilder();

            string totalSql = string.Format("  select count(*) from dbo.KPIInsInfo i  left join dbo.EmpDep d on i.DepID=d.DepID left join dbo.EmpPersonBase p on i.EvaluatorID=p.UserID where i.ValidMark=1  and i.DepID ='{0}'", dept);

            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            strSql.Append("  select * from  (select top " + totalCount + " * from ( select top ");
            strSql.Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1));
            strSql.Append(" KPIInsInfoID,i.DepID,AssessInd,AssessTimes,");
            strSql.Append(" [Weight]=(case when charindex('M',AssessTimes )>0 and isnull(convert(varchar(10),Weight_M),'')<>'' then LEFT(isnull(convert(varchar(10),Weight_M),''),(len(Weight_M)-3))+'%/' else '' end +");
            strSql.Append(" case when charindex('S',AssessTimes )>0 and isnull(convert(varchar(10),Weight_S),'')<>'' then  LEFT(isnull(convert(varchar(10),Weight_S),''),(len(Weight_S)-3))+'%/' else '' end +");
            strSql.Append(" case when charindex('H',AssessTimes)>0 and isnull(convert(varchar(10),Weight_H),'')<>'' then  LEFT(isnull(convert(varchar(10),Weight_H),''),(len(Weight_H)-3))+'%/' else '' end +");
            strSql.Append(" case when charindex('Y',AssessTimes )>0 and isnull(convert(varchar(10),Weight_Y),'')<>'' then  LEFT(isnull(convert(varchar(10),Weight_Y),''),(len(Weight_Y)-3))+'%/' else '' end ");
            strSql.Append(" ),Indicator,");
            strSql.Append(" AssessCriteria,SourceFromDeptID,EvaluatorID,Comments,");
            strSql.Append(" i.DispOrder,i.Creator,i.CreateTime,i.Editor,i.EditTime,i.ValidMark, ");
            strSql.Append(" DepName+','+PersonName SourceData from dbo.KPIInsInfo i ");
            strSql.Append(" left join dbo.EmpDep d on i.SourceFromDeptID=d.DepID");
            strSql.Append(" left join dbo.EmpPersonBase p on i.EvaluatorID=p.PersonID");
            strSql.Append(" where i.ValidMark=1 and i.DepID=@DepID ");
            strSql.Append("  and KPIInsInfoID not in(select top   ");
            strSql.Append((pageIndex - 1) * pageSize);
            strSql.Append("  KPIInsInfoID from dbo.KPIInsInfo i ");
            strSql.Append(" where i.ValidMark=1 and i.DepID=@DepID order by i.DispOrder ))a ");
            strSql.Append(" order by a.DispOrder)  a order by a.DispOrder  ");

            SqlParameter[] parameters = {
					new SqlParameter("@DepID", SqlDbType.Int,4)
            };
            parameters[0].Value = dept;


            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 获得一条数据
        /// </summary>
        public DataSet GetOne(int KPIInsInfoID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select KPIInsInfoID,i.DepID,AssessInd,AssessTimes,Weight_M,Weight_S,Weight_H,Weight_Y,Indicator,");
            strSql.Append(" AssessCriteria,SourceFromDeptID,EvaluatorID,Comments,");
            strSql.Append(" i.DispOrder,i.Creator,i.CreateTime,i.Editor,i.EditTime,i.ValidMark, ");
            strSql.Append(" DepName+','+PersonName SourceData from dbo.KPIInsInfo i ");
            strSql.Append(" left join dbo.EmpDep d on i.SourceFromDeptID=d.DepID");
            strSql.Append(" left join dbo.EmpPersonBase p on i.EvaluatorID=p.PersonID");
            strSql.Append(" where i.KPIInsInfoID = @KPIInsInfoID");

            SqlParameter[] parameters = {
					new SqlParameter("@KPIInsInfoID", SqlDbType.Int,4)
};
            parameters[0].Value = KPIInsInfoID;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获得某个部门的权重之和
        /// </summary>
        public DataSet GetWeight(int deptid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select sum(Weight_M) Weight_M,sum(Weight_S) Weight_S,sum(Weight_H) Weight_H,sum(Weight_Y) Weight_Y from  dbo.KPIInsInfo where ValidMark=1 and DepID=@DepID");

            SqlParameter[] parameters = {
					new SqlParameter("@DepID", SqlDbType.Int,4)
                    };
            parameters[0].Value = deptid;

            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 获取考核项进行考核选择
        /// </summary>
        /// <returns></returns>
        public DataSet getKPIInsInfo(string userid, string datatimeNow)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select DepID,DepName,AssessTimesVal,deptCategory from ( select DepID,DepName, ");
            strSql.Append(" AssessTimesVal =case when charindex('M',AssessTimesVal)>0 and (today between M_Start and M_End ) then 'M' else '' end+','+ ");
            strSql.Append(" case when charindex('S',AssessTimesVal)>0 and (monthVal in(1,4,7,10)) and (today between S_Start and S_End ) then 'S' else '' end+','+ ");
            strSql.Append(" case when charindex('H',AssessTimesVal)>0 and (monthVal in(1,7)) and (today between H_Start and H_End ) then 'H' else '' end+','+ ");
            strSql.Append(" case when charindex('Y',AssessTimesVal)>0 and (monthVal in(1)) and (today between Y_Start and Y_End ) then 'Y' else '' end ");
            strSql.Append(" ,deptCategory, M_Start,  M_End, S_Start, S_End, H_Start,  H_End, Y_Start,  Y_End,monthVal,today ");
            strSql.Append(" from ( select s.*, r1.AssessStartDay M_Start,r1.AssessEndDay M_End, ");
            strSql.Append(" r2.AssessStartDay S_Start,r2.AssessEndDay S_End, ");
            strSql.Append(" r3.AssessStartDay H_Start,r3.AssessEndDay H_End, ");
            strSql.Append(" r4.AssessStartDay Y_Start,r4.AssessEndDay Y_End, ");
            strSql.Append(DateTime.Now.Month + " monthVal, ");
            strSql.Append(DateTime.Now.Day + " today from (select * from  ");
            strSql.Append(" (select e.DepID,e.DepName,isnull(stuff((select ','+rtrim(AssessTimes)   ");
            strSql.Append(" from dbo.KPIInsInfo k where  k.ValidMark=1 and  k.DepID=e. DepID  ");
            strSql.Append(" and k.EvaluatorID='" + userid + "'");
            strSql.Append(" for XML path('')),1,1,''),'') AssessTimesVal ,s.KeyValue deptCategory  ");
            strSql.Append("  from dbo.EmpDep e  left join dbo.SysDictionarySub s on s.ID=e.DepCategory where  DepCategory=195 )t ");
            strSql.Append(" where  isnull(t.AssessTimesVal,'')<>'' union   ");
            strSql.Append(" select * from ( select e.DepID,e.DepName,isnull(stuff((select ','+rtrim(AssessTimes) ");
            strSql.Append(" from (select kbf.AssessTimes from dbo.KPIBasicInfo kbf ");
            strSql.Append(" where  kbf.ValidMark=1 and KPIBasicIndexID in  ");
            strSql.Append(" (select KPIBasicIndexID from dbo.KPIBasicIndex where ValidMark=1 and KPIBasicItemID in( ");
            strSql.Append(" select KPIBasicItemID from dbo.KPIBasicItem where ValidMark=1 ");
            strSql.Append(" and EvaluatorID='" + userid + "'");
            strSql.Append(" )) )k  for XML path('')),1,1,''),'') AssessTimesVal,s.KeyValue deptCategory ");
            strSql.Append(" from dbo.EmpDep e  left join dbo.SysDictionarySub s on s.ID=e.DepCategory ");
            strSql.Append(" where  DepCategory=196 )t where isnull(t.AssessTimesVal,'')<>'') s");
            strSql.Append(" left join dbo.KPIRange r1 on r1.AssessTimes='M'");
            strSql.Append(" left join dbo.KPIRange r2 on r2.AssessTimes='S'");
            strSql.Append(" left join dbo.KPIRange r3 on r3.AssessTimes='H'");
            strSql.Append(" left join dbo.KPIRange r4 on r4.AssessTimes='Y')p)a ");
            strSql.Append(" where a.AssessTimesVal<>',,,'");
            strSql.Append(" order by deptCategory");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取某部门的考核结果
        /// </summary>
        /// <param name="DepID"></param>
        /// <param name="AssessTimes"></param>
        /// <param name="AssessYear"></param>
        /// <param name="AssessDate"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet getKPIIns(int DepID, string AssessTimes, string AssessYear, string AssessDate, string userid, out long totalCount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select ii.KPIInsInfoID,ii.DepID,ii.AssessInd,ii.AssessTimes,");
            if (AssessTimes == "M")
            {
                strSql.Append("LEFT(ii.Weight_M,(len(ii.Weight_M)-3)) Weight,");
            }
            if (AssessTimes == "S")
            {
                strSql.Append("LEFT(ii.Weight_S,(len(ii.Weight_S)-3)) Weight,");
            }
            if (AssessTimes == "H")
            {
                strSql.Append("LEFT(ii.Weight_H,(len(ii.Weight_H)-3)) Weight,");
            }
            if (AssessTimes == "Y")
            {
                strSql.Append("LEFT(ii.Weight_Y,(len(ii.Weight_Y)-3)) Weight,");
            }
            strSql.Append(" ii.Indicator,ii.AssessCriteria");
            strSql.Append(" ,ii.SourceFromDeptID,ii.EvaluatorID,");
            strSql.Append(" '' KPIInsID,");
            strSql.Append("  '' AssessYear,");
            strSql.Append(" '' AssessDate,");
            strSql.Append(" '' ActualValue,");
            strSql.Append(" '' Rate,");
            strSql.Append(" '' Score");
            strSql.Append(" , ii.Comments,ii.DispOrder ");
            strSql.Append(" ,DepName+','+PersonName SourceData");
            strSql.Append(" from dbo.KPIInsInfo ii");
            strSql.Append(" left join dbo.EmpDep d on ii.SourceFromDeptID=d.DepID");
            strSql.Append(" left join dbo.EmpPersonBase p on ii.EvaluatorID=p.PersonID");
            strSql.Append(" where ii.ValidMark=1 and ii.DepID='" + DepID + "' and ii.EvaluatorID='" + userid + "' and ii.AssessTimes like '%" + AssessTimes + "%'");



            StringBuilder sbKPIIns = new StringBuilder();

            sbKPIIns.Append(" select * from (");
            sbKPIIns.Append(" select ii.KPIInsInfoID,ii.DepID,ii.AssessInd,ii.AssessTimes,");
            if (AssessTimes == "M")
            {
                sbKPIIns.Append("LEFT(ii.Weight_M,(len(ii.Weight_M)-3)) Weight,");
            }
            if (AssessTimes == "S")
            {
                sbKPIIns.Append("LEFT(ii.Weight_S,(len(ii.Weight_S)-3)) Weight,");
            }
            if (AssessTimes == "H")
            {
                sbKPIIns.Append("LEFT(ii.Weight_H,(len(ii.Weight_H)-3)) Weight,");
            }
            if (AssessTimes == "Y")
            {
                sbKPIIns.Append("LEFT(ii.Weight_Y,(len(ii.Weight_Y)-3)) Weight,");
            }
            sbKPIIns.Append(" ii.Indicator,ii.AssessCriteria ,");
            sbKPIIns.Append("  ii.SourceFromDeptID,ii.EvaluatorID,i.KPIInsID,AssessYear,AssessDate,");
            sbKPIIns.Append(" ActualValue,Rate,Score,ii.Comments,ii.DispOrder  ,DepName+','+PersonName SourceData from dbo.KPIIns i");
            sbKPIIns.Append(" left join KPIInsInfo ii on i.KPIInsInfoID=ii.KPIInsInfoID ");
            sbKPIIns.Append(" left join dbo.EmpDep d  on ii.SourceFromDeptID=d.DepID ");
            sbKPIIns.Append("  left join dbo.EmpPersonBase p on ii.EvaluatorID=p.PersonID");
            sbKPIIns.Append(" where i.ValidMark=1 and i.DeptID='" + DepID + "' and i.EvaluatorID='" + userid + "'");
            sbKPIIns.Append(" and i.AssessTimes='" + AssessTimes + "'  and i.AssessYear='" + AssessYear + "' and i.AssessDate='" + AssessDate + "'");
            sbKPIIns.Append(" union ");
            sbKPIIns.Append(" select ii.KPIInsInfoID,ii.DepID,ii.AssessInd,ii.AssessTimes,");
            if (AssessTimes == "M")
            {
                sbKPIIns.Append("LEFT(ii.Weight_M,(len(ii.Weight_M)-3)) Weight,");
            }
            if (AssessTimes == "S")
            {
                sbKPIIns.Append("LEFT(ii.Weight_S,(len(ii.Weight_S)-3)) Weight,");
            }
            if (AssessTimes == "H")
            {
                sbKPIIns.Append("LEFT(ii.Weight_H,(len(ii.Weight_H)-3)) Weight,");
            }
            if (AssessTimes == "Y")
            {
                sbKPIIns.Append("LEFT(ii.Weight_Y,(len(ii.Weight_Y)-3)) Weight,");
            }
            sbKPIIns.Append(" ii.Indicator,ii.AssessCriteria ,ii.SourceFromDeptID,ii.EvaluatorID, ''");
            sbKPIIns.Append("  KPIInsID,  ''");
            sbKPIIns.Append(" AssessYear, '' AssessDate,'' ActualValue, '' Rate,'' Score , ii.Comments,ii.DispOrder ");
            sbKPIIns.Append(" ,DepName+','+PersonName SourceData from dbo.KPIInsInfo ii ");
            sbKPIIns.Append(" left join dbo.EmpDep d on ii.SourceFromDeptID=d.DepID ");
            sbKPIIns.Append(" left join dbo.EmpPersonBase p on ii.EvaluatorID=p.PersonID ");
            sbKPIIns.Append(" where ii.ValidMark=1 and ii.DepID='" + DepID + "' and ii.EvaluatorID='" + userid + "'");
            sbKPIIns.Append(" and ii.AssessTimes like '%" + AssessTimes + "%' and ii.KPIInsInfoID not in ");
            sbKPIIns.Append(" (select KPIInsInfoID from KPIIns i where i.ValidMark=1 and i.DeptID='" + DepID + "' and i.EvaluatorID='" + userid + "'");
            sbKPIIns.Append(" and i.AssessTimes='" + AssessTimes + "'  and i.AssessYear='" + AssessYear + "' and i.AssessDate='" + AssessDate + "')");
            sbKPIIns.Append(" ) t ");

            string totalKPIInsSql = " select count(*) from (" + sbKPIIns.ToString() + ")a";
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalKPIInsSql).ToString());

            sbKPIIns.Append(" order by DispOrder");

            if (totalCount == 0)//如果没有考核过
            {
                string totalSql = " select count(*) from (" + strSql.ToString() + ")a";
                totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

                strSql.Append(" order by ii.DispOrder");
                return DbHelperSQL.Query(strSql.ToString());
            }
            else
                return DbHelperSQL.Query(sbKPIIns.ToString());

        }

        /// <summary>
        /// 获取最大的排序号并加减数额
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="num"></param>
        /// <param name="colunmName"></param>
        /// <param name="maxOrmin"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public object getOrder(string tableName, string num, string colunmName, string maxOrmin, string whereStr)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select ");
            strSql.Append(maxOrmin);
            strSql.Append(" (" + colunmName + ")" + num + " from " + tableName + " where ValidMark=1");
            if (whereStr != "")
                strSql.Append(whereStr);

            return DbHelperSQL.ExecuteScalar(strSql.ToString());
        }
    }
}

